﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using RESTVeterinariaServices.Dominio;
using System.Data.SqlClient;
using System.ServiceModel;

namespace RESTVeterinariaServices.Persistencia
{
    public class VeterinarioDAO
    {

        public Veterinario Crear(Veterinario objACrear)
        {
            Veterinario ObjCreado = null;
            try
            {
                int Pk = 0;
                using (SqlConnection con = new SqlConnection(ConexionUtil.ObtenerCadena()))
                {
                    string query = "SELECT isnull(MAX(IdVeterinario)+1,1) FROM Veterinario";
                    using (SqlCommand sentencia = new SqlCommand(query, con))
                    {
                        Pk = (int)sentencia.ExecuteScalar();
                    }
                }

                using (SqlConnection con = new SqlConnection(ConexionUtil.ObtenerCadena()))
                {
                    string query = "INSERT INTO Veterinario VALUES ";
                    query += "(@IdVeterinario,@txtVeterinarioNombre,@txtVeterinarioApellidoP,@txtVeterinarioApellidoM,@numCVP)";
                    objACrear.IdVeterinario = Pk;
                    using (SqlCommand com = new SqlCommand(query, con))
                    {
                        com.Parameters.Add(new SqlParameter("@idVeterinario", objACrear.IdVeterinario));
                        com.Parameters.Add(new SqlParameter("@txtVeterinarioNombre", objACrear.txtVeterinarioNombre));
                        com.Parameters.Add(new SqlParameter("@txtVeterinarioApellidoP", objACrear.txtVeterinarioApellidoP));
                        com.Parameters.Add(new SqlParameter("@txtVeterinarioApellidoM", objACrear.txtVeterinarioApellidoM));
                        com.Parameters.Add(new SqlParameter("@numCVP", objACrear.numCVP));

                        com.ExecuteNonQuery();
                    }
                }
                ObjCreado = obtener(objACrear.IdVeterinario.ToString());
                return ObjCreado;
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Veterinario obtener(string strPk)
        {
            try
            {
                Veterinario P = null;
                string sql = "SELECT IdVeterinario,txtVeterinarioNombre,txtVeterinarioApellidoP,txtVeterinarioApellidoM,numCVP FROM Veterinario WHERE IdVeterinario=@IdVeterinario";

                using (SqlConnection con = new SqlConnection(ConexionUtil.ObtenerCadena()))
                {
                    con.Open();
                    using (SqlCommand com = new SqlCommand(sql, con))
                    {
                        com.Parameters.Add(new SqlParameter("@IdVeterinario", strPk));
                        using (SqlDataReader res = com.ExecuteReader())
                        {
                            if (res.Read())
                            {
                                P = new Veterinario()
                                {
                                    IdVeterinario = (int)res["idVeterinario"],
                                    txtVeterinarioApellidoM = (string)res["txtVeterinarioApellidoM"],
                                    txtVeterinarioApellidoP = (string)res["txtVeterinarioApellidoP"],
                                    txtVeterinarioNombre = (string)res["txtVeterinarioNombre"],
                                    numCVP = (string)res["numCVP"]
                                };
                            }
                        }
                    }
                }
                return P;
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<Veterinario> ListarTodos()
        {
            try
            {
                List<Veterinario> list = new List<Veterinario>();
                string sql = "SELECT * FROM Veterinario ";

                using (SqlConnection con = new SqlConnection(ConexionUtil.ObtenerCadena()))
                {
                    con.Open();
                    using (SqlCommand com = new SqlCommand(sql, con))
                    {
                        using (SqlDataReader res = com.ExecuteReader())
                        {
                            while (res.Read())
                            {
                                Veterinario c = new Veterinario()
                                {
                                    IdVeterinario = (int)res["idVeterinario"],
                                    txtVeterinarioApellidoM = (string)res["txtVeterinarioApellidoM"],
                                    txtVeterinarioApellidoP = (string)res["txtVeterinarioApellidoP"],
                                    txtVeterinarioNombre = (string)res["txtVeterinarioNombre"],
                                    numCVP = (string)res["numCVP"]
                                };
                                list.Add(c);
                            }
                        }
                    }
                }
                return list;
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Veterinario Modificar(Veterinario objAModificar)
        {
            Veterinario objModificado = null;
            using (SqlConnection con = new SqlConnection(ConexionUtil.ObtenerCadena()))
            {
                string sql = "UPDATE Veterinario SET txtVeterinarioNombre = @txtVeterinarioNombre, txtVeterinarioApellidoP = @txtVeterinarioApellidoP,";
                sql += "txtVeterinarioApellidoM = @txtVeterinarioApellidoM,numCVP = @numCVP WHERE IdVeterinario = @IdVeterinario";
                 
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    com.Parameters.Add(new SqlParameter("@idVeterinario", objAModificar.IdVeterinario));
                    com.Parameters.Add(new SqlParameter("@txtVeterinarioNombre", objAModificar.txtVeterinarioNombre));
                    com.Parameters.Add(new SqlParameter("@txtVeterinarioApellidoP", objAModificar.txtVeterinarioApellidoP));
                    com.Parameters.Add(new SqlParameter("@txtVeterinarioApellidoM", objAModificar.txtVeterinarioApellidoM));
                    com.Parameters.Add(new SqlParameter("@numCVP", objAModificar.numCVP));
                    com.ExecuteNonQuery();
                }
            }
            objModificado = obtener(objAModificar.IdVeterinario.ToString());
            return objModificado;
        }

    }
}